Geek Answers Handbook

Would using transitions be a good idea in such a situation?

Context: Oracle Database 10.

In a fairly large table (several million records) we recently started to see some performance issues. The table has some special behaviors / conditions.

  • it is mostly written once, and then again not changed.
  • during the first day or so, entries are classified from 0..N (allows you to call this class of columns). records can be reclassified several times during this first day.
  • added new entries with class 0, which means "not yet classified"
  • every hour or so the process classifies new reocrds and gives them a new class from 1..N
  • all readers are only interested in class 1
  • all records older than a day are unlikely to change their class,> 1 is cleared after a few days.

Now that most access is done for class 1, this column is often involved in queries (class = 1) along with other conditions. We have an index in the class column, and then again for some other columns.

To my question: we are now going to split this table into a class. As I understand it, this will speed up indexing / working with data, since class = 1 is already separated from the rest of the data, and therefore access to it is implicitly more efficient. Is it correct?

If you agree that this is a good idea, I will continue to read this topic!

Thanks Greetings

Update 2010.11.30

. , :) ( ). , , .

+5
3

, ?

, . , . , , , . , , , , .

, , , , , . , ,

CREATE INDEX idx_new_entries
    ON your_table( (CASE WHEN class = 0 THEN primary_key ELSE null END) );

CREATE INDEX idx_class1_entries
    ON your_table( (CASE WHEN class = 1 THEN primary_key ELSE null END) );


CREATE VIEW vw_new_entries
AS
SELECT (CASE WHEN class = 0 THEN primary_key ELSE null END) primary_key,
       <<list of columns>>
  FROM your_table
 WHERE class = 0

CREATE VIEW vw_class1_entries
AS
SELECT (CASE WHEN class = 1 THEN primary_key ELSE null END) primary_key,
       <<list of columns>>
  FROM your_table
 WHERE class = 1

, PRIMARY_KEY, , , , . , .

+4

MB? ? ? ? ? . , - ?

, ,

, , .

+3

Yepp, that sounds good.

There are other alternatives to this, but an easy fix is ​​the section.

-1
source

More articles:


All Articles