Discussions
Categories
- 196.9K All Categories
- 2.2K Data
- 239 Big Data Appliance
- 1.9K Data Science
- 450.4K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 546 SQLcl
- 4K SQL Developer Data Modeler
- 187.1K SQL & PL/SQL
- 21.3K SQL Developer
- 295.9K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.6K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 443 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
Partition vs Bitmap

i currently learning database administration and i wanted to ask on what scenario do we need to create a partition?
from what i know we create a bitmap index on column that have low disctinct value(gender,marital status,etc) and there is a query that have 1 or more predicate using those low distinct value column
for example
select * from employees where gender='M' and marital_status ='Married'
and for the partition we create it in order to prevent oracle to do a FTS when our query return most of the row in the table. instead oracle can just read from a partition
for example assuming emp have 1k record(200 M 800 F)
select * from employees where Gender='F';
with this query the optimizer will opting for fts because our query return 80% of the row.and it is good to create a list partition here based on gender. does this method efficient on performace tuning? can anyone tell on what scenario we create a partition?
thanks
Answers
-
bitmap
Bitmap indexes implies Serialization. That's like having the db parameter
_SLOW=TRUE
.It can speed up SELECTS, but at a cost for other DML operations.
In my uses, it's not about the "low cardinality" of the answers in a column, but the "low cardinality" of your query results.
Eg finding out that a particular song, sung by Adam Sandler about Tinder results for other Eskimos in his tribe, is true
I've used them to speed up ad hoc queries on final reporting tables.
Billy uses them to show you can count 42B rows in under 1s.
Partition
"Prevent FTS" is one use for Partitions.
The primary use I've seen is for data management.
Need to remove 1 mo worth of data? Drop a Partition
Also, research ILM in the Data warehouse guide. This helps automate the task of moving chunks of old data for you. (Eg move 1mo worth of 3yr old data from tablespace on SSD to compressed read-only tablespace on SATA)
-
Thank for replying mike
i have a couple of question
1.what u mean by ad hoc query is query using bind variable in where clause? or is there a different meaning?(from what i know ad hoc query is a type of query where result set is depent on the value supplied to a variable)
2."Need to remove 1 mo worth of data? Drop a Partition" even if we drop a partition the actual table still have the data from that partition right? and for what purpose we dropping this data? for memory or just deleting old data?
-
Ad hoc
This is what most end users want to perform.
APEX IR searches are ad hoc queries. Especially those faceted searches.
Bitmap indexes could drastically improve multi-column queries of these types even on a 1 B row table.
But, INSERT/UPDATE/DELETES would be horrible. You'll want to do infrequent bulk operations from a single transaction only. Tools like DBMS_PARALLEL_EXECUTE will cause the crud operation to take longer if parallel_level is higher than 1. (I learned this the hard way)
Drop Partition
You usually remove old data for legal reasons.
Partitions are individuals tables magically joined together to act as 1 table. If you read the history of Oracle, Partitions started in v7.3 as Partitioned VIEWs.
So, when you DROP a Partition, you are actually dropping a table.
Which then brings up Partition EXCHANGE. In this case you are actually swapping one table for another. And because the DD is only swapping the pointers, this is extremely fast.
You can swap in/out a table's worth of data in the blink of an eye. (I'm ignoring INDEXES)
Again, I find Partitions more useful for data management. But queries performance shouldn't be ignored.
-
thanks for the partition part i got a little insigth about it.
but still have question about bitmap
can you explain example of "faceted searches" is it like result set with low cardinality?(FYI first time hear faceted word so i do not understand)
-
Faceted Search example using APEX.