Following are the step where a situation occurs for secondary index.
1) :- I am creating a table exam_year_name can store the rank of exam for exam.
cqlsh:payroll> CREATE TABLE exam_year_name ( ... exam_year int, ... exam_name text, ... examlist_name text, ... rank int, ... PRIMARY KEY ((exam_year, exam_name), rank) ... ); cqlsh:payroll> Select * from exam_year_name; exam_year | exam_name | rank | examlist_name -----------+-----------+------+--------------- (0 rows) cqlsh:payroll> INSERT INTO exam_year_name (exam_year, exam_name ,examlist_name,rank) ... VALUES (2015,'Teacher Eligibility Test', 'CTET',1); cqlsh:payroll> INSERT INTO exam_year_name (exam_year, exam_name ,examlist_name,rank) ... VALUES (2015,'Teacher Eligibility Test', 'UPTET',2); cqlsh:payroll> INSERT INTO exam_year_name (exam_year, exam_name ,examlist_name,rank) ... VALUES (2015,'Teacher Eligibility Test', 'MPTET',3); cqlsh:payroll> INSERT INTO exam_year_name (exam_year, exam_name ,examlist_name,rank) ... VALUES (2015,'Public Service Commission', 'UPPSC',1); cqlsh:payroll> INSERT INTO exam_year_name (exam_year, exam_name ,examlist_name,rank) ... VALUES (2015,'Public Service Commission', 'PBPSC',2); cqlsh:payroll> INSERT INTO exam_year_name (exam_year, exam_name ,examlist_name,rank) ... VALUES (2015,'Public Service Commission', 'MPPSC',3); cqlsh:payroll> cqlsh:payroll> cqlsh:payroll> select * from exam_year_name; exam_year | exam_name | rank | examlist_name -----------+---------------------------+------+--------------- 2015 | Public Service Commission | 1 | UPPSC 2015 | Public Service Commission | 2 | PBPSC 2015 | Public Service Commission | 3 | MPPSC 2015 | Teacher Eligibility Test | 1 | CTET 2015 | Teacher Eligibility Test | 2 | UPTET 2015 | Teacher Eligibility Test | 3 | MPTET (6 rows)
cqlsh:payroll> select * from exam_year_name where exam_year=2015 and exam_name='Public Service Commission'; exam_year | exam_name | rank | examlist_name -----------+---------------------------+------+--------------- 2015 | Public Service Commission | 1 | UPPSC 2015 | Public Service Commission | 2 | PBPSC 2015 | Public Service Commission | 3 | MPPSC (3 rows)
3) :- I am query to listing of the rankings for a
particular year. This query will fail if
only the first column is used in the conditional operator because the table has
a composite partition key.
4) :- At this situation, we need secondary index is
created for the exam_year, and the query will succeed.cqlsh:payroll> select * from exam_year_name where exam_year=2015; Fail to execute InvalidRequest: Error from server: code=2200 [Invalid query] message="Partition key parts: exam_name must be restricted as other parts are"
cqlsh:payroll> CREATE INDEX eyear ON exam_year_name (exam_year); cqlsh:payroll> select * from exam_year_name where exam_year=2015; exam_year | exam_name | rank | examlist_name -----------+---------------------------+------+--------------- 2015 | Public Service Commission | 1 | UPPSC 2015 | Public Service Commission | 2 | PBPSC 2015 | Public Service Commission | 3 | MPPSC 2015 | Teacher Eligibility Test | 1 | CTET 2015 | Teacher Eligibility Test | 2 | UPTET 2015 | Teacher Eligibility Test | 3 | MPTET (6 rows)5) :- A clustering column can also be used to create an index. An index is created on rank, and used in a query.
cqlsh:payroll> CREATE INDEX erank ON exam_year_name (rank); cqlsh:payroll> select * from exam_year_name where rank=1; exam_year | exam_name | rank | examlist_name -----------+---------------------------+------+--------------- 2015 | Public Service Commission | 1 | UPPSC 2015 | Teacher Eligibility Test | 1 | CTET (2 rows) cqlsh:payroll>
No comments:
Post a Comment