=============
Optimizer Statistics.
Optimizer statistics describe details about the database and the objects in the database. The query optimizer uses these statistics to
choose the best execution plan for each SQL statement.
Optimizer statistics include the following:
Table statistics :- (Number of rows,Number of blocks,Average row length)
Column statistics :- (Number of distinct values (NDV) in column, Number of nulls in column, Data distribution (histogram), Extended statistics)
Index statistics :- (Number of leaf blocks, Levels, Clustering factor)
System statistics :- (I/O performance and utilization, CPU performance and utilization)
Oracle recommends that you enable automatic optimizer statistics collection. In this case, the database automatically collects optimizer
statistics for tables with absent or stale statistics. If fresh statistics are required for a table, then the database collects
them both for the table and associated indexes. Oracle can determine the statle stats when statistics_level parameter is set to all or typical.
Manual Statistics:-
Manaul statistics are recommended for high volatile tables, or high load tables which adds 10% or more to make the stats stale.
Volatile tables:-
tables that are deleted or truncated and rebuilt during the course of the day.
The statistics on these tables can be null. When Oracle Database encounters a table with no statistics,
the database dynamically gathers the necessary statistics as part of query optimization. The OPTIMIZER_DYNAMIC_SAMPLING parameter
controls this dynamic sampling feature. Set this parameter to a value of 2 (default) or higher. You can set the statistics to null
by deleting and then locking the statistics:
BEGIN
DBMS_STATS.DELETE_TABLE_STATS('MANZOOR','EMP');
DBMS_STATS.LOCK_TABLE_STATS('MANZOOR','EMP');
END;
/
The statistics on these tables can be set to values that represent the typical state of the table. You should gather
statistics on the table when the tables has a representative number of rows, and then lock the statistics.
This may be more effective than automatic optimizer statistic collection, because any statistics generated on the table
during the overnight batch window may not be the most appropriate statistics for the daytime workload.
Bulk load tables:-
For tables that are bulk-loaded, run the statistics-gathering procedures on the tables immediately following the load process.
Preferably, run the procedures as part of the same script or job that is running the bulk load.
About optimizer_dynamic_sampling parameter.
The dynamic sampling level controls both when dynamic sampling is triggered, and the size of the sample that the optimizer
uses to gather the statistics. You can set the dynamic sampling level using either the OPTIMIZER_DYNAMIC_SAMPLING
initialization parameter or a cursor hint.
value between 0 and 10.
Level 0
Do not use dynamic sampling.
Level 1
Use dynamic sampling for all tables that have not been analyzed, but only if the following criteria are met:
There is at least 1 unanalyzed, nonpartitioned table in the query.
This unanalyzed table has no indexes.
This unanalyzed table has more blocks than the number of blocks that would be used for dynamic sampling of this table.
Sample blocks - 32
Level 2
Use dynamic sampling if least one table in the statement has no statistics.
Sample blocks - 64
Level 3
Use dynamic sampling if any of the following conditions is true:
The statement meets level 2 criteria.
The statement has one or more expressions used in the where clause predicates, for example, WHERE SUBSTR(CUSTLASTNAME,1,3).
Sample blocks 64
Level 4
Use dynamic sampling if any of the following conditions is true:
The statement meets level 3 criteria.
The statement uses complex predicates (an OR or AND operator between multiple predicates on the same table).
Sample blocks 64
Level 5 - Use dynamic sampling if the statement meets level 4 criteria. - Sample blocks 128
Level 6 - Use dynamic sampling if the statement meets level 4 criteria. - Sample blocks 256
Level 7 - Use dynamic sampling if the statement meets level 4 criteria. - Sample blocks 512
Level 8 - Use dynamic sampling if the statement meets level 4 criteria. - Sample blocks 1024
Level 9 - Use dynamic sampling if the statement meets level 4 criteria. - Sample blocks 4086
Level 10 - Use dynamic sampling for all statements - All blocks.
Eg.
SQL> show parameter optimizer_dynamic_sampling;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 2
SQL> create table emp (empno number, empname varchar2(30), salary number, deptid number, report_mgr_id number) ;
Table created.
SQL> select table_name, tablespace_name from user_tables;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
EMP MANZOOR_TBLSPC
SQL> begin
2 for i in 1..200000 loop
3 insert into emp values (i, dbms_random.string('U',30), trunc(dbms_random.value(50000,80000)), trunc(dbms_random.value(10,20)), trunc(dbms_random.value(100,110)));
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> select count(*) from emp;
COUNT(*)
----------
200000
SQL> create index idx_emp on emp (empno);
Index created.
SQL> select table_name, last_analyzed from user_tables;
TABLE_NAME LAST_ANAL
------------------------------ ---------
EMP
SQL> select * from emp where empno = 45679;
EMPNO EMPNAME SALARY DEPTID REPORT_MGR_ID
---------- ------------------------------ ---------- ---------- -------------
45679 LOXTKJSYLGYYPGYISYYEPSAJMIXGGH 73230 11 106
SQL> @xplan
Enter value for sql_id: f3ghj5k1n358x
Enter value for child_number: 0
old 1: select * from table (dbms_xplan.display_cursor('&sql_id',&child_number))
new 1: select * from table (dbms_xplan.display_cursor('f3ghj5k1n358x',0))
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID f3ghj5k1n358x, child number 0
-------------------------------------
select * from emp where empno = 45679
Plan hash value: 306890541
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 69 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_EMP | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=45679)
Note
-----
- dynamic sampling used for this statement (level=2)
-- Since there is no statistics on the table, optimizer uses the dynamic sampling to identify the optimal exeuction plan.
SQL> select sum(salary) from emp where empno between 10000 and 40000;
SUM(SALARY)
-----------
1949803108
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID c0nmnacujw150, child number 0
-------------------------------------
select sum(salary) from emp where empno between 10000 and 40000
Plan hash value: 2083865914
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 411 (100)| |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
|* 2 | TABLE ACCESS FULL| EMP | 45788 | 1162K| 411 (1)| 00:00:05 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("EMPNO">=10000 AND "EMPNO"<=40000))
Note
-----
- dynamic sampling used for this statement (level=2)
--
SQL> exec dbms_stats.gather_table_stats('MANZOOR','EMP');
PL/SQL procedure successfully completed.
SQL> select sum(salary) from emp where empno between 50000 and 100000;
SUM(SALARY)
-----------
3252503275
SQL> @xplan
Enter value for sql_id: acutaaka6dksc
Enter value for child_number: 0
old 1: select * from table (dbms_xplan.display_cursor('&sql_id',&child_number))
new 1: select * from table (dbms_xplan.display_cursor('acutaaka6dksc',0))
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID acutaaka6dksc, child number 0
-------------------------------------
select sum(salary) from emp where empno between 50000 and 100000
Plan hash value: 2083865914
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 412 (100)| |
| 1 | SORT AGGREGATE | | 1 | 10 | | |
|* 2 | TABLE ACCESS FULL| EMP | 50002 | 488K| 412 (1)| 00:00:05 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("EMPNO"<=100000 AND "EMPNO">=50000))
Add some more data's.
SQL> insert into emp select * from emp where rownum < 500000;
499999 rows created.
SQL> commit;
Commit complete.
SQL> select * from user_tab_modifications;
no rows selected
1. Table modification info stays in shared pool and flushed into dictionary by Oracle automatically.
You can explicity do it by calling dbms_stats.flush_database_monitoring_info.
SQL> exec dbms_stats.flush_database_monitoring_info;
PL/SQL procedure successfully completed.
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP TRU DROP_SEGMENTS
------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- --------- --- -------------
EMP 3499999 0 0 29-SEP-15 NO 0
SQL> select STALE_STATS , table_name from user_tab_statistics;
STA TABLE_NAME
--- ------------------------------
YES EMP
-- After the load the statistics become stale.
SQL> select count(*) from emp;
COUNT(*)
----------
3699999
-- Lets delete the stats now.
SQL> exec dbms_stats.delete_table_stats('MANZOOR','EMP');
PL/SQL procedure successfully completed.
SQL> select sum(salary) from emp where empno between 87363 and 100000;
SUM(SALARY)
----------------------------------------
15511815804
select sum(salary) from emp where empno between 87363 and 100000
Plan hash value: 2353115127
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 8494 (100)| |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 101K| 2584K| 8494 (1)| 00:01:42 |
|* 3 | INDEX RANGE SCAN | IDX_EMP | 101K| | 354 (0)| 00:00:05 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("EMPNO">=87363 AND "EMPNO"<=100000)
Note
-----
- dynamic sampling used for this statement (level=2)
-- Now lets gather the statiscs and compare.
SQL> exec dbms_stats.gather_table_stats('MANZOOR','EMP' , no_invalidate => FALSE);
PL/SQL procedure successfully completed.
SQL> select sum(salary) from emp where empno between 87363 and 100000;
SUM(SALARY)
----------------------------------------
15511815804
SQL_ID f3gdkx3p7v6z2, child number 0
-------------------------------------
select sum(salary) from emp where empno between 87363 and 100000
Plan hash value: 2083865914
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 12737 (100)| |
| 1 | SORT AGGREGATE | | 1 | 10 | | |
|* 2 | TABLE ACCESS FULL| EMP | 233K| 2283K| 12737 (1)| 00:02:33 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("EMPNO"<=100000 AND "EMPNO">=87363))
Histograms
==========
For data with uniform distribution, the optimizer makes accurate guesses at the cost of executing a particular statement,
relying on a high and low value and a count of the rows in the column to determine this. However, when the data is not
uniformly distributed (skewed), the optimizer cannot accurately estimate the selectivity of a query. For columns which do not have
uniform data distribution, Oracle will allow you to store histograms describing the data distribution of a particular column.
When the histograms required
1) On columns which are used in the where clause and have high skewed data.
2) Recommended to let oracle determine the the using method opt auto based on data distribution and load on columns.
When the histrogram not required
1) The column data is uniformley distibuted.
2) The column is not used in where clause.
3) The column is unique and used only with equality predicates.
Note: In legacy versions, if all predicates on the column used bind variables then histograms were not advised but,
since Oracle 9i, when Peeking into user-defined bind variables was introduced as a default, this is no longer the case.
Method_opt accepted values
For all [indexed] [hidden] columns size
For columns [size clause] columns [size clause]...
Size clause --> Size {integer / repeat / auto / skewonly }
Integer -> Number of buckets in the range [1,254].
Repeat -> Collects histograms only on the columns which already has histograms.
AUTO -> Oracle determines the columns to collect histograms based on the data distribution and workload of the column.
Skewonly -> Oracle determines the columns to collect histograms based on the data distribution of the column.
SQL> desc emp;
Name Null? Type
----------------- -------- ------------
EMPNO NUMBER
EMPNAME VARCHAR2(30)
SALARY NUMBER
DEPTID NUMBER
REPORT_MGR_ID NUMBER
GENDER VARCHAR2(1)
SQL> select count(*) from emp;
COUNT(*)
----------------------------------------
3699999
SQL> select
count (distinct EMPNO) "empno",
count (distinct EMPNAME) "EMPNAME",
count (distinct salary) "SALARY",
count (distinct DEPTID) "DEPTID",
count (distinct REPORT_MGR_ID) "REPORT_MGR_ID",
count (distinct GENDER) "GENDER"
from emp
/
empno EMPNAME SALARY DEPTID REPORT_MGR_ID GENDER
---------- --------- --------- --------- ------------- ---------
200000 200000 29968 10 10 2
Eg.
We have queries which are used in where clause empno , salary, depid & Gender.
EMPNO -> Data distribution is ok.
SQL> select deptid , count(*) from emp group by deptid;
DEPTID COUNT(*)
--------- ----------------------------------------
13 372794
11 369849
14 367352
17 368629
18 368819
15 368292
19 370009
10 369930
16 368766
12 375559
- Data is almost equally distibuted
SQL> select gender , count(*) from emp group by gender;
G COUNT(*)
- ----------------------------------------
M 2774998
F 925001
-- Data is highly skewed
What histrogram holds.
Lets gather the histograms empno, salary , dept_id & geneder,
PROCEDURE GATHER_TABLE_STATS
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OWNNAME VARCHAR2 IN
TABNAME VARCHAR2 IN
PARTNAME VARCHAR2 IN DEFAULT
ESTIMATE_PERCENT NUMBER IN DEFAULT
BLOCK_SAMPLE BOOLEAN IN DEFAULT
METHOD_OPT VARCHAR2 IN DEFAULT
DEGREE NUMBER IN DEFAULT
GRANULARITY VARCHAR2 IN DEFAULT
CASCADE BOOLEAN IN DEFAULT
STATTAB VARCHAR2 IN DEFAULT
STATID VARCHAR2 IN DEFAULT
STATOWN VARCHAR2 IN DEFAULT
NO_INVALIDATE BOOLEAN IN DEFAULT
STATTYPE VARCHAR2 IN DEFAULT
FORCE BOOLEAN IN DEFAULT
CONTEXT CCONTEXT IN DEFAULT
begin
dbms_stats.gather_table_stats
('MANZOOR', 'EMP', METHOD_OPT => 'FOR COLUMNS SIZE AUTO EMPNO FOR COLUMNS SIZE AUTO SALARY FOR COLUMNS SIZE AUTO DEPTID FOR COLUMNS SIZE AUTO GENDER');
end;
/
PL/SQL procedure successfully completed.
SQL> select owner, TABLE_NAME, COLUMN_NAME, NUM_DISTINCT, LOW_VALUE, HIGH_VALUE, NUM_NULLS, NUM_BUCKETS, histogram from dba_tab_col_statistics
where OWNER ='MANZOOR' and TABLE_NAME = 'EMP' order by 3;
OWNER TABLE_NAME COLUMN_NAME NUM_DISTINCT LOW_VALUE HIGH_VALUE NUM_NULLS NUM_BUCKETS HISTOGRAM
-------- ---------- -------------------- -------------------- ---------- ---------- -------------------- -------------------- ---------------
MANZOOR EMP DEPTID 10 C10B C114 0 1 NONE
MANZOOR EMP EMPNO 201968 C102 C315 0 1 NONE
MANZOOR EMP GENDER 2 46 4D 0 1 NONE
MANZOOR EMP SALARY 29962 C306 C3086464 0 1 NONE
SQL> select owner, TABLE_NAME, COLUMN_NAME, NUM_DISTINCT, UTL_RAW.CAST_TO_NUMBER(LOW_VALUE), UTL_RAW.CAST_TO_NUMBER(HIGH_VALUE), NUM_NULLS,
NUM_BUCKETS, histogram from dba_tab_col_statistics where COLUMN_NAME in ('DEPTID','EMPNO','SALARY') order by 3
OWNER TABLE_NAME COLUMN_N NUM_DISTINCT UTL_RAW.CAST_TO_NUMBER(LOW_VALUE) UTL_RAW.CAST_TO_NUMBER(HIGH_VALUE) NUM_NULLS NUM_BUCKETS HISTOGRAM
-------- ---------- -------- -------------------- --------------------------------- ---------------------------------- --------- -------------------- ---------------
MANZOOR EMP DEPTID 10 10 19 0 1 NONE
MANZOOR EMP EMPNO 201968 1 200000 0 1 NONE
MANZOOR EMP SALARY 29962 50000 79999 0 1 NONE
SQL> select owner, TABLE_NAME, COLUMN_NAME, NUM_DISTINCT, UTL_RAW.CAST_TO_varchar2(LOW_VALUE), UTL_RAW.CAST_TO_varchar2(HIGH_VALUE), NUM_NULLS, NUM_BUCKETS, histogram
from dba_tab_col_statistics where COLUMN_NAME ='GENDER';
OWNER TABLE_NAME COLUMN_N NUM_DISTINCT UTL_RAW.CA UTL_RAW.CAST_TO_VARC NUM_NULLS NUM_BUCKETS HISTOGRAM
-------- ---------- -------- -------------------- ---------- -------------------- --------- -------------------- ---------------
MANZOOR EMP GENDER 2 F M 0 1 NONE
SQL> select * from dba_tab_histograms where owner = 'MANZOOR' AND TABLE_NAME = 'EMP' order by 3,4
OWNER TABLE_NAME COLUMN_N ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALUE
-------- ---------- -------- -------------------- -------------------- ----------------------------------------
MANZOOR EMP DEPTID 0 10
MANZOOR EMP DEPTID 1 19
MANZOOR EMP EMPNO 0 1
MANZOOR EMP EMPNO 1 200000
MANZOOR EMP GENDER 0 3.63460780097438E+35
MANZOOR EMP GENDER 1 3.99806858107182E+35
MANZOOR EMP SALARY 0 50000
MANZOOR EMP SALARY 1 79999
As from the above we could see the columns statistics gathered
1) Number of distinct values
2) Minimum value & Maximum value
3) Number of nulls.
These information will be used by the optimizer for generating an optimized explain plan.
Here the number of buckets created by auto is 1.
No. of buckets = No. of rows of information.
Here the number for buckets is 1.
if NDV <= Buckets then
Frequence histrogram is created
else
Height Balanced histogram is created.
SQL> exec dbms_stats.delete_table_stats('MANZOOR','EMP');
PL/SQL procedure successfully completed.
begin
dbms_stats.gather_table_stats
('MANZOOR', 'EMP', METHOD_OPT => 'FOR COLUMNS SIZE 20 EMPNO FOR COLUMNS SIZE 20 SALARY FOR COLUMNS SIZE 20 DEPTID FOR COLUMNS SIZE 20 GENDER');
end;
/
SQL> select owner, TABLE_NAME, COLUMN_NAME, NUM_DISTINCT, LOW_VALUE, HIGH_VALUE, NUM_NULLS, NUM_BUCKETS, histogram from dba_tab_col_statistics
where OWNER ='MANZOOR' and TABLE_NAME = 'EMP' order by 3;
2
OWNER TABLE_NAME COLUMN_N NUM_DISTINCT LOW_VALUE HIGH_VALUE NUM_NULLS NUM_BUCKETS HISTOGRAM
-------- ---------- -------- -------------------- ---------- ---------- --------- -------------------- ---------------
MANZOOR EMP DEPTID 10 C10B C114 0 10 FREQUENCY
MANZOOR EMP EMPNO 201968 C102 C315 0 20 HEIGHT BALANCED
MANZOOR EMP GENDER 2 46 4D 0 2 FREQUENCY
MANZOOR EMP SALARY 29962 C306 C3086464 0 20 HEIGHT BALANCED
SQL> select owner, TABLE_NAME, COLUMN_NAME, NUM_DISTINCT, UTL_RAW.CAST_TO_NUMBER(LOW_VALUE), UTL_RAW.CAST_TO_NUMBER(HIGH_VALUE), NUM_NULLS,
NUM_BUCKETS, histogram from dba_tab_col_statistics where COLUMN_NAME in ('DEPTID','EMPNO','SALARY') order by 3 2
3 /
OWNER TABLE_NAME COLUMN_N NUM_DISTINCT UTL_RAW.CAST_TO_NUMBER(LOW_VALUE) UTL_RAW.CAST_TO_NUMBER(HIGH_VALUE) NUM_NULLS NUM_BUCKETS HISTOGRAM
-------- ---------- -------- -------------------- --------------------------------- ---------------------------------- --------- -------------------- ---------------
MANZOOR EMP DEPTID 10 10 19 0 10 FREQUENCY
MANZOOR EMP EMPNO 201968 1 200000 0 20 HEIGHT BALANCED
MANZOOR EMP SALARY 29962 50000 79999 0 20 HEIGHT BALANCED
SQL> select owner, TABLE_NAME, COLUMN_NAME, NUM_DISTINCT, UTL_RAW.CAST_TO_varchar2(LOW_VALUE), UTL_RAW.CAST_TO_varchar2(HIGH_VALUE), NUM_NULLS, NUM_BUCKETS, histogram
from dba_tab_col_statistics where COLUMN_NAME ='GENDER';
OWNER TABLE_NAME COLUMN_N NUM_DISTINCT UTL_RAW.CA UTL_RAW.CAST_TO_VARC NUM_NULLS NUM_BUCKETS HISTOGRAM
-------- ---------- -------- -------------------- ---------- -------------------- --------- -------------------- ---------------
MANZOOR EMP GENDER 2 F M 0 2 FREQUENCY
SQL> select * from dba_tab_histograms where owner = 'MANZOOR' AND TABLE_NAME = 'EMP' order by 3,4
2 /
OWNER TABLE_NAME COLUMN_N ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALUE
-------- ---------- -------- -------------------- -------------------- ----------------------------------------
MANZOOR EMP DEPTID 557 10
MANZOOR EMP DEPTID 1122 11
MANZOOR EMP DEPTID 1661 12
MANZOOR EMP DEPTID 2236 13
MANZOOR EMP DEPTID 2733 14
MANZOOR EMP DEPTID 3296 15
MANZOOR EMP DEPTID 3871 16
MANZOOR EMP DEPTID 4414 17
MANZOOR EMP DEPTID 4970 18
MANZOOR EMP DEPTID 5498 19
MANZOOR EMP EMPNO 0 14
MANZOOR EMP EMPNO 1 9707
MANZOOR EMP EMPNO 2 19962
MANZOOR EMP EMPNO 3 29758
MANZOOR EMP EMPNO 4 40448
MANZOOR EMP EMPNO 5 51153
MANZOOR EMP EMPNO 6 60887
MANZOOR EMP EMPNO 7 70471
MANZOOR EMP EMPNO 8 79255
MANZOOR EMP EMPNO 9 88360
MANZOOR EMP EMPNO 10 97256
MANZOOR EMP EMPNO 11 107909
MANZOOR EMP EMPNO 12 118557
MANZOOR EMP EMPNO 13 128245
MANZOOR EMP EMPNO 14 138706
MANZOOR EMP EMPNO 15 149709
MANZOOR EMP EMPNO 16 159761
MANZOOR EMP EMPNO 17 169731
MANZOOR EMP EMPNO 18 180242
MANZOOR EMP EMPNO 19 190316
MANZOOR EMP EMPNO 20 200000
MANZOOR EMP GENDER 1361 3.63460780097438E+35
MANZOOR EMP GENDER 5498 3.99806858107182E+35
MANZOOR EMP SALARY 0 50001
MANZOOR EMP SALARY 1 51611
MANZOOR EMP SALARY 2 53183
MANZOOR EMP SALARY 3 54665
MANZOOR EMP SALARY 4 56138
MANZOOR EMP SALARY 5 57635
MANZOOR EMP SALARY 6 59200
MANZOOR EMP SALARY 7 60779
MANZOOR EMP SALARY 8 62241
MANZOOR EMP SALARY 9 63537
MANZOOR EMP SALARY 10 64948
MANZOOR EMP SALARY 11 66411
MANZOOR EMP SALARY 12 67947
MANZOOR EMP SALARY 13 69419
MANZOOR EMP SALARY 14 70813
MANZOOR EMP SALARY 15 72313
MANZOOR EMP SALARY 16 73903
MANZOOR EMP SALARY 17 75449
MANZOOR EMP SALARY 18 77162
MANZOOR EMP SALARY 19 78670
MANZOOR EMP SALARY 20 79999
54 rows selected.
Here we could see that for deptid the number of distinct values is 10 hence it has created only 10 bukets, and also for the Gender
the number of distinct value is 2 hence it is created 2 buckets, on the other hand number of distinct value for salary is 29962 which
is greater than the number of buckets which creates height balanced histograms.
