Skip to Main Content
  • Questions
  • What performs better for historical tables, Where END_DATE is null or Where CURRENT_FLAG = 1 ?

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Jeff.

Asked: June 02, 2024 - 2:26 am UTC

Last updated: June 10, 2024 - 6:52 am UTC

Version: 21.2

Viewed 100+ times

You Asked

Hi Tom,
My first time asking a question here. Am querying a historical table which not only has START_DATE and END_DATE, it also has CURRENT_FLAG where CURRENT_FLAG = 1 corresponds to END_DATE is null.

I assume that the CURRENT_FLAG column (all 0s and a 1) would perform much faster than the END_DATE column, but the table is not big enough to test this. I'm all about coding best practices, and I would love to know which performs better.

Jeff Stubing

and Connor said...

In the absence of anything else, they will be identical in terms of performance.

SQL> create table t tablespace largets
  2  as select d.*,
  3    case when mod(object_id,5) != 0 then 0 else 1 end flag,
  4    case when mod(object_id,5) != 0 then created else null end end_Date
  5  from dba_objects d,
  6   ( select 1 from dual
  7     connect by level <= 100 );

Table created.

SQL> set timing on
SQL> select count(*) from t where flag = 1;

  COUNT(*)
----------
   1729700

Elapsed: 00:00:00.61
SQL> select count(*) from t where end_date is null;

  COUNT(*)
----------
   1729700

Elapsed: 00:00:00.60


Where matters more is the volume of rows of interst, and whether is it then worth indexing them etc. In the case above, 1.7million rows are returned so an index is *unlikely* to be useful.

If it was just a small amount of rows, I could index the data so that only the rows I want are present in the index, eg

create index IX1 on t ( end_date ) -- only picks up the rows with a non-null end date
create index IX2 on t ( case when flag = 1 then 1 end ) -- only picks up the rows with a 1 for flag.

Similarly, depending on requirements, you could explore partitioning

Rating

  (1 rating)

Comments

Jeff Stubing, June 07, 2024 - 10:42 am UTC

Thank you! Great response!
Connor McDonald
June 10, 2024 - 6:52 am UTC

glad to help

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.