Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Geraldo.

Asked: June 30, 2021 - 1:04 pm UTC

Last updated: July 05, 2021 - 1:32 pm UTC

Version: 19.7

Viewed 1000+ times

You Asked

Hello, ASK TOM Team.

I have a 14 columns table, millions of rows, highly used. I need to add another column (FLAG_STATE), which will have two different values (0/1); is like "boolean". 

CREATE TABLE SCHEMA.TABLE1
(
  ID NUMBER GENERATED BY DEFAULT AS IDENTITY ( START WITH 1 MAXVALUE 9999999999999999999999999999 MINVALUE 1 NOCYCLE CACHE 20 NOORDER NOKEEP) NOT NULL,
...
  CUSTOMER                 VARCHAR2(15 BYTE) NOT NULL,
  DOC                        VARCHAR2(9 BYTE) NOT NULL,
...
FLAG_STATE                         NUMBER(1,0)
)


This table often is queried by customer and doc columns. A customer can have several docs (sometimes the same doc 3 or 4 times). I have an index on (customer,doc). Right now, I need to add FLAG_STATE column to the table.

What kind of index should I use if I want to filter by FLAG_STATE too? Is OK with the existing index? Should I add the new column to the existing index? Should I use null values as a state and just flag rows with "1" when needed? What else am I missing?

Thanks in advanced.

Regards,

and Chris said...

What kind of queries are you running on this table? Are they OK with the existing index? What performance difference does adding FLAG_STATE to the index make? What's the (expected) data distribution of values for the new column - 50:50. 80:20, 99:1?

If the queries are fast enough with the existing indexes => you probably don't need to change the indexes

If the queries are too slow you need to consider your options. Creating an index including FLAG_STATE is just one of these.

Depending on the queries and the data distribution changing the index may give the speed up you need. Or might make no difference. Or may even make queries slower!

Answering my questions above will help you determine whether it's worth adding/changing your indexes to include this column.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Martin Rose, July 05, 2021 - 2:13 pm UTC

I would consider partitioning on FLAG_STATE (with ROW_MOVEMENT ENABLED).

Where indexes fail, partitioning often succeeds.

They are contrary to each other & work well when used together.

Follow Up

Geraldo, July 05, 2021 - 2:33 pm UTC

Ok. Thanks, Chris.

I will review those questions and determine what fits best.

Regards,
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.