CODEWITHSUNDEEP
sqloracle-database
SRCM
SRCM

Reputation: 271

Table vs View - Oracle

One of my Oracle tables contain more than 200 million records. Querying the table sometimes takes upto 30 minutes. I am trying to create a view on the table so as to limit the records for about 0.5 million. Will this decrease the querying time?

Upvotes: 0

Views: 391

Answers (3)

Mark J. Bobak
Mark J. Bobak

Reputation: 14385

It depends. You need to understand your data, which subset of the data you're most interested in, and determine how to best implement the physical model. (heap table, IOT, bitmap index, B*Tree index, single table hash cluster, etc)

If you have a query, for example:

select * from some_big_table where col_a = 32457;

and it returns, say, 500,000 rows out of 200,000,000, but it takes many minutes to run, because there's no index on col_a, and is forced to do a full table scan, then creating a view:

create view my_view as select * from some_big_table where col_a = 32457;

and then select from the view, you're still going to have the same problem.

You need to look at the physical model, and what is the most efficient way to store and/or index the data, to optimize the access path(s) utilized to retrieve the data.

Upvotes: 1

René Nyffenegger
René Nyffenegger

Reputation: 40499

The view won't be slower (or faster) as the underllying query.

So, if your view is 

create view v as
    select * from t
     where rownum < 500000;

then

select * from v;

takes +/- the same time as 

select * from t
  where rownum < 500000;

Maybe you want to take a look into materialized views. Depending on the query, they might significantelly speed up queries (at the cost of more disk usage).

Edit As per your comment (UI reports need to be generated for only recent data (i.e. last one week or one month). Therefore I need only the latest data...) I think want to partition your table. May I suggest this link for an introduction to partioned tables? Especially the part about partition pruning.

Upvotes: 1

Adel
Adel

Reputation: 1468

This is not the way to decrease the speed of querying, please follow the following:

  • First of all make index on the fields which are normally searching by.
  • Do not use where x in (select), but use inner join by PKs.
  • Do not use functions to get value from another table, but use join, (this will open another sessions otherwise)
  • Try if you can to normalize and separate the BLOB, CLOB fields away from the table to another table, and better if it is layed in another tablespace.
  • Give more memory to the given schema.
  • use also the following instractions:

Part 1 discusses the use of full-table scans part 2 discusses how and when to use indexes. Part 3 tells you how you can optimise joins. Part 4 discusses how to use views to get a high performance database. Part 5 tells you why your database should NOT be normalised. In Part 6 you can learn how to use stored procedures to sky-rocket Oracle performance. Finally, part 7 shows you how to use sub queries to boost Oracle performance

references to http://www.smart-soft.co.uk/Oracle/oracle-performance-tuning-part1.htm

Upvotes: 1

Related Questions