Oracle SQL – Scenario Questions Part 4

In this blog we will explore some more Oracle SQL scenario questions. Here we will cover more about indexes.


Scenario 1: Create table T_ACCOUNT, inserted some data & created index on three columns (ACCOUNT_ID,ACCOUNT_TYPE,ACCOUNT_STATUS).

Now running below query, will index picked up by Oracle optimizer or not?

SELECT * FROM T_ACCOUNT where ACCOUNT_TYPE ='C' and ACCOUNT_STATUS='O';
CREATE TABLE T_ACCOUNT
  ( account_id     NUMBER ,
    account_name   VARCHAR2(100) ,
    account_type   VARCHAR2(10) ,
    account_status CHAR(1),
    open_date      DATE );
CREATE INDEX IDX_ACCOUNT_1 ON T_ACCOUNT(ACCOUNT_ID,ACCOUNT_TYPE,ACCOUNT_STATUS);

Explanation : The order of the columns can make a difference. IDX_ACCOUNT_1 would be most useful if you fire such queries. Table would be full accessed if you don’t follow the order.

SELECT * FROM T_ACCOUNT where ACCOUNT_ID=100 and ACCOUNT_TYPE ='C' and ACCOUNT_STATUS='O';
SELECT * FROM T_ACCOUNT where ACCOUNT_ID=100 and ACCOUNT_TYPE ='C';
SELECT * FROM T_ACCOUNT where ACCOUNT_ID=100;

Example: I have used my columns as per the order of index. Here Table accessed by Index.

EXPLAIN PLAN FOR SELECT * FROM T_ACCOUNT where ACCOUNT_ID=100 and ACCOUNT_TYPE ='C';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Now using columns not in order or index. Here tables is full accessed.

EXPLAIN PLAN FOR SELECT * FROM T_ACCOUNT where ACCOUNT_TYPE ='C' and ACCOUNT_STATUS='O';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Scenario 2: Can you create the Unique Index on the column having some dupes records.

Explanation: No

Scenario 3: Created one table with primary key, so oracle created the default index on that column. What will happen if you are going to create unique index on same column.

Explanation: Cannot create the index on already indexed columns.

You can first create the Index & then enforce the constraint.

DROP TABLE T_SAMPLE;
CREATE TABLE T_SAMPLE
  ( ID NUMBER ,
  DATA_SOURCE NUMBER
  );
CREATE UNIQUE INDEX IDX_T_SAMPLE ON T_SAMPLE(ID);

ALTER TABLE T_SAMPLE ADD CONSTRAINT PK_T_SAMPLE PRIMARY KEY(ID);

Though you can create index if the primary key having more than one column.

CREATE TABLE T_SAMPLE
  ( ID NUMBER ,
  DATA_SOURCE NUMBER,
  CONSTRAINT T_SAMPLE_pk PRIMARY KEY (ID,DATA_SOURCE)
  );
CREATE INDEX IDX_T_SAMPLE ON T_SAMPLE(ID);

Scenario 4: Created the tables & index on two columns ID, CODE. In query I’m using the condition like ID||CODE = ‘100E’ . Will optimizer pick up the index, if not how to solve this, as some times we have to use like this.

CREATE TABLE T_SAMPLE ( ID NUMBER, CODE VARCHAR2(1));
CREATE INDEX T_SAMPLE_IDX ON T_SAMPLE (ID,CODE);

INSERT INTO T_SAMPLE
SELECT level,
  DECODE(mod(level, 2), 0, 'E', 'O')
FROM dual
  CONNECT BY level <= 100000;
BEGIN
  dbms_stats.gather_table_stats(USER, 'T_SAMPLE');
END;
/

select * from T_SAMPLE where Id =100 and code='E';-- Index Picked
select * from T_SAMPLE where Id||code='100E';-- Index Not Picked

Explanation: Optimizer will not pick the index in this case. You have to create one functional index.

After creating functional index:

CREATE INDEX T_SAMPLE_IDX2 ON T_SAMPLE (ID||CODE);
SELECT * FROM T_SAMPLE WHERE Id||code='100E' ;

Scenario 5: See below scenario, column is number datatype & while querying you have provided the single quotes. Will Index picked by optimizer.

DROP TABLE T_SAMPLE;
CREATE TABLE T_SAMPLE( ID NUMBER);
CREATE UNIQUE INDEX T_SAMPLE_IDX ON T_SAMPLE  (ID);

INSERT INTO T_SAMPLE
SELECT level
FROM dual
  CONNECT BY level <= 100000;
BEGIN
  dbms_stats.gather_table_stats(USER, 'T_SAMPLE');
END;
/
select * from T_SAMPLE where Id =100 ; -- Index Picked
select * from T_SAMPLE where Id =to_char('100');
Index got picked up by optimizer

Scenario 6: See below scenario, column is number datatype & while querying you have provided the single quotes. Will Index picked by optimizer.

DROP TABLE T_SAMPLE;

CREATE TABLE T_SAMPLE( ID VARCHAR2(4000));
CREATE UNIQUE INDEX T_SAMPLE_IDX ON T_SAMPLE (ID);

INSERT INTO T_SAMPLE
SELECT level
FROM dual
  CONNECT BY level <= 100000;

BEGIN
  dbms_stats.gather_table_stats(USER, 'T_SAMPLE');
END;
/

select * from T_SAMPLE where Id ='100';
select * from T_SAMPLE where Id = 100 ;-- Table Access Full

Visit below blogs for other SQL Scenario questions.


Thanks!

Happy Learning! Your feedback would be appreciated!

3 thoughts on “Oracle SQL – Scenario Questions Part 4

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s