CODEWITHSUNDEEP
sqlperformanceoracle-databaseselectindexing
Steve
Steve

Reputation: 11

Oracle SQL: One select takes to long, another select are fast

I have two select on the same view. One Select will be filtered with the primary key, the other select will be filterd on a non unique index. The used view are complicated. The Select with Primary Key needs approximately 15 seconds. The select with the non unique index needs 0,5 seconds.

Why is the query which using the primary key so slow?

I use "EXPLAIN PLAN FOR" to create a execution plan for both.

The execution plan for fast select: fast select

The execution plan for slow select: slow select

--Pseudocode
create table TableA
(
   ID number,  --(Primary Key)
   ProjectID number, --(Not unique index)
   TableB_id number, --(Foreign Key to Table TableB)
   TableC_id number, --(Foreign Key to Table TableC)
   TableD_id number  --(Foreign Key to Table TableD)
);



Create view viewX
as
Select 
      ID as TableB_ID, 
      0 as TableC_ID, 
      0 as TableD_ID, 
      Value1, 
      Value2
   from TableB
union all
Select 
      0 as TableB_ID, 
      ID as TableC_ID, 
      0 as TableD_ID, 
      Value1, 
      value2
   from TableC
union all
Select 
      0 as TableB_ID, 
      0 as TableC_ID, 
      id as TableD_ID, 
      value1, 
      value2
   from viewz;



Create view viewA
as
Select 
       t.id, 
       t.ProjectID, 
       x.TableB_ID, 
       x.TableC_ID, 
       x.TableD_ID
   from TableA t
   inner join viewX x
   on t.TableB_ID = x.TableB_ID and
      t.TableC_ID = x.TableC_ID and
      t.TableD_ID = x.TableD_ID;

--this select needs o,5 seconds
Select *
   from ViewA 
   where ProjectID = 2220;


--this select needs 15 seconds
Select *
   from viewA
   where id = 5440;

The select on TableA and on ViewX separatly are fast.

--this select needs 0,5 seconds
select *
   from TableA
   where id = 5440;

Result: ID = 5440, ProjektID = 2220, TableB_ID = 123, TableC_ID = 5325, TableD_ID = 7654

--this select needs 0,3 seconds
Select *
   viewX x
   where TableB_ID = 123 and
         TableC_ID = 5325 and
         TableD_ID = 7654;

Thanks for your support

Upvotes: 1

Views: 430

Answers (1)

Roger Cornejo
Roger Cornejo

Reputation: 1547

I would say it is because the optimizer will decompose the select against the view to selects against he base tables. In the second case, you are not union-ing all the rows of the other tables, just the rows that meet the where clause for that table, therefore the second query is faster because it has to go through less rows.

Upvotes: 1

Related Questions