CODEWITHSUNDEEP
databaseoracle-database
tete
tete

Reputation: 5009

Select from a view using where clause on indexed column in Oracle Database

We have a Oracle database table called StockDailyQuote which contains billions of rows. There are two indexes: on TradeDate (a Date field) and on StockTicker (a string field). This table is actually a partitioned table and on each TradeDate there is an individual table. So we never query against it using 

SELECT * FROM StockDailyQuote

because it is impractical to get any result. Instead we always use a WHERE clause on TradeDate and the the speed is acceptable.

Now we have compiled a view out of StockDailyQuote table (joined some other tables to get useful information). Because we can't specify the TradeDate at this stage so our view looks like this (this is a simplified version): 

create or replace view MyStockQuoteView as
SELECT t1.StockTicker, t2.CompanyName, t1.TradeDate, t1.ClosePrice
FROM StockDailyQuote t1 join CompanyInstruction t2 on t1.StockTicker = 
t2.StockTicker

And I always try to query against this view with a WHERE clause on TradeDate column. I thought when oracle sees my query SELECT * FROM MyStockQuoteView where TradeDate = '20170726', it would be smart enough to add the indexed where clause to the sql and then send it to the engine so the query should be as equally fast as SELECT * from StockDailyQuote where TradeDate = '20170726', assuming my other joins do not take much time. But it doesn't behave like that: it takes really long time to return values. I can only assume it queries against the whole view and then uses the where on the returned value. This makes this query impractical to use. So how can I solve my problem? One solution is to make a procedure and run it daily and save one day's data to a table. But is there some other options?

Upvotes: 1

Views: 1909

Answers (1)

Artem Kharkunov
Artem Kharkunov

Reputation: 66

It would be nice to see the query plan SELECT * FROM MyStockQuoteView where TradeDate = '20170726'. Oracle normally understands what it needs to read to the partition inside the view. You can try to explicitly specify the hint, for example:

SELECT /* + full(MyStockQuoteView.StockDailyQuote) */ * FROM MyStockQuoteView where TradeDate = '20170726'

Upvotes: 1

Related Questions