| # Copyright 2016, Quickstep Research Group, Computer Sciences Department, |
| # University of Wisconsin—Madison. |
| # |
| # Licensed under the Apache License, Version 2.0 (the "License"); |
| # you may not use this file except in compliance with the License. |
| # You may obtain a copy of the License at |
| # |
| # http://www.apache.org/licenses/LICENSE-2.0 |
| # |
| # Unless required by applicable law or agreed to in writing, software |
| # distributed under the License is distributed on an "AS IS" BASIS, |
| # WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. |
| # See the License for the specific language governing permissions and |
| # limitations under the License. |
| |
| CREATE TABLE foo3 (col1 INT, col2 INT); |
| CREATE INDEX idx1 ON foo3(col1) USING CSBTREE; |
| SELECT * FROM foo3; |
| -- |
| +-----------+-----------+ |
| |col1 |col2 | |
| +-----------+-----------+ |
| +-----------+-----------+ |
| == |
| CREATE INDEX idx2 ON foo3(col1) USING CSBTREE; |
| -- |
| ERROR: The relation foo3 already defines this index on the given attribute(s). |
| == |
| CREATE INDEX idx2 ON foo3(col2) USING CSBTREE; |
| SELECT * FROM foo3; |
| -- |
| +-----------+-----------+ |
| |col1 |col2 | |
| +-----------+-----------+ |
| +-----------+-----------+ |
| == |
| # Compound index creation should succeed, even when |
| # separate indices are defined on individual attributes. |
| CREATE INDEX idx3 ON foo3(col1, col2) USING CSBTREE; |
| SELECT * FROM foo3; |
| -- |
| +-----------+-----------+ |
| |col1 |col2 | |
| +-----------+-----------+ |
| +-----------+-----------+ |
| == |
| # Bloom filter index is not currently implemented. |
| CREATE INDEX idx4 ON foo3(col1, col2) USING BLOOMFILTER; |
| SELECT * FROM foo3; |
| -- |
| +-----------+-----------+ |
| |col1 |col2 | |
| +-----------+-----------+ |
| +-----------+-----------+ |
| == |
| # Specifying no columns will index all the columns. |
| # Calling INSERT will force creation of the block. |
| CREATE TABLE smaTable1 (int_attr int, str_attr VARCHAR(20)); |
| CREATE INDEX smaIndex ON smaTable1 USING SMA; |
| INSERT INTO smaTable1 VALUES (1, 'val1'); |
| SELECT COUNT(*) FROM smaTable1; |
| -- |
| +--------------------+ |
| |COUNT(*) | |
| +--------------------+ |
| | 1| |
| +--------------------+ |
| == |
| # SMA can also specify an index on selected columns. |
| # Calling INSERT will force creation of the block. |
| CREATE TABLE smaTable2 (int_attr int, str_attr VARCHAR(20)); |
| CREATE INDEX smaIndex2 ON smaTable2(int_attr) USING SMA; |
| INSERT INTO smaTable2 VALUES (1, 'val1'); |
| SELECT COUNT(*) FROM smaTable2; |
| -- |
| +--------------------+ |
| |COUNT(*) | |
| +--------------------+ |
| | 1| |
| +--------------------+ |