Skip to Main Content
  • Questions
  • Select statement goes with Table Storage full in Dev environment but uses index range scan in SIT environment

Breadcrumb

Dev Live Dev Intro

We are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August and September. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Connor McDonald

Thanks for the question, Sara.

Asked: August 12, 2020 - 8:10 pm UTC

Answered by: Connor McDonald - Last updated: August 13, 2020 - 6:29 am UTC

Category: Database Administration - Version: 12c

Viewed 100+ times

You Asked

Hi Team,

I have a same table in 2 environments Development and SIT. Both have the same index pptcode_1_3.

Environment - DEV
Table name - pptcode
number of rows - 103819
execution time - 9 secs

when i run execution plan on development it does TABLE ACCESS STORAGE FULL for the select statement with the where condition.

Environment - SIT
TABLE name - pptcode
number of rows 57109
execution time - 1 sec

When I run execution plan on SIT it does go with TABLE ACCESS BY INDEX ROWID and INDEX RANGE SCAN

How can i make the query to use index in development and run in 1 second.

and we said...

You could add a hint to the code to force the index, but from the data you've provided, the tables obviously have different data in each environment, so I'm not sure there is a justification to force the same plan for both.

This really depends on your requirements:

- If you want the best plans for each environment, make sure your statistics are up to data in each environment

- If you want dev to match SIT for execution plans, then look at exporting/importing the schema level optimizer stats. You'll get similar plans, but since the data is different, you may get different response time characteristics.

- If you want lock in a plan from SIT and force Dev to take it, look at SQL Plan Management to grab the plan from SIT and import it into Dev.

and you rated our response

  (1 rating)

Reviews

on Clustering Factor of Index.

August 15, 2020 - 3:31 am UTC

Reviewer: Rajeshwaran, Jeyabal

Just one more thing to add up.

for this index pptcode_1_3. compare the clustering factor with num of rows to num_of_blocks between SIT and Dev.

That could provide some insights why the optimizer in Dev databse is not picking up the index, thought it got twice the volume compare to SIT.
select i.index_name,i.clustering_factor,i.num_rows,t.blocks
from user_indexes i,
  user_tables t 
where i.index_name = <your_index_name_goes_here>
and i.table_name = t.table_name

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.