1. Top 5 rows (employees) from each department in a single query
use Row_Number()over(partition by dept_id ) Then Filter
2. ALL / ANY > Query
https://oracle-base.com/articles/misc/all-any-some-comparison-conditions-in-sql
3. Update a partition key value.
It is not possible to update partition key column value. We need to first enable table movement.
Alter table table name enable movement;
Now we can update the key value column.
External tables are created using the SQL CREATE TABLE...ORGANIZATION EXTERNAL statement.
TYPE — specifies the type of external table. Each type of external table is supported by its own access driver.
ORACLE_LOADER — this is the default access driver. It loads data from external tables to internal tables. The data must come from text data files.
ORACLE_DATAPUMP — this access driver can perform both loads and unloads. The data must come from binary dump files.
ORACLE_HIVE — extracts data stored in Apache HIVE.
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/LISTAGG.html
As an Aggregate Function
SELECT department_id "Dept.", LISTAGG(last_name, '; ' ON OVERFLOW TRUNCATE '...') WITHIN GROUP (ORDER BY hire_date) "Employees" FROM employees GROUP BY department_id ORDER BY department_id;
Dept. Employees ------ ------------------------------------------------------------ 10 Whalen 20 Hartstein; Fay 30 Raphaely; Khoo; Tobias; Baida; Himuro; Colmenares 40 Mavris 50 Kaufling; Ladwig; Rajs; Sarchand; Bell; Mallin; Weiss; Davie s; Marlow; Bull; Everett; Fripp; Chung; Nayer; Dilly; Bissot ; Vollman; Stiles; Atkinson; Taylor; Seo; Fleaur; ... (23) 70 Baer . . .
As an Analytic Function
SELECT department_id "Dept", hire_date "Date", last_name "Name", LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hire_date, last_name) OVER (PARTITION BY department_id) as "Emp_list" FROM employees WHERE hire_date < '01-SEP-2003' ORDER BY "Dept", "Date", "Name"; Dept Date Name Emp_list ----- --------- --------------- --------------------------------------------- 30 07-DEC-02 Raphaely Raphaely; Khoo 30 18-MAY-03 Khoo Raphaely; Khoo
ALTER SESSION SET CURSOR_SHARING = FORCE;
https://oracle-base.com/articles/11g/online-table-redefinition-enhancements-11gr1
https://medium.com/@dmitry.romanoff/how-to-check-fragmentation-in-an-oracle-database-a575b8a6e2ea
Once you’ve identified fragmentation, consider the following methods to address it:
ALTER TABLE YOUR_TABLE_NAME MOVE;
ALTER INDEX YOUR_INDEX_NAME REBUILD;
DECLARE
l_num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.copy_table_dependents(
uname => 'TEST',
orig_table => 'REDEF_TAB',
int_table => 'REDEF_TAB2',
copy_indexes => 1, -- Default
copy_triggers => TRUE, -- Default
copy_constraints => TRUE, -- Default
copy_privileges => TRUE, -- Default
ignore_errors => FALSE, -- Default
num_errors => l_num_errors,
copy_statistics => FALSE, -- Default
copy_mvlog => FALSE); -- Default
DBMS_OUTPUT.put_line('num_errors=' || l_num_errors);
END;
/
https://oracle-base.com/articles/12c/in-memory-column-store-12cr1
The In-Memory Column Store (IM column store) was the headline feature of the 12.1.0.2 patchset. This features allows you to store columns, tables, partitions and materialized views in memory in a columnar format, rather than the typical row format. The advantage of having data in memory is obvious, but the columnar storage lends itself extremely well to analytic queries found in business intelligence products.
ALTER SYSTEM SET INMEMORY_SIZE=2G SCOPE=SPFILE;
ALTER SYSTEM SET INMEMORY_QUERY=ENABLE;
CREATE TABLE im_tab ( id NUMBER ) INMEMORY;CREATE TABLE noim_tab ( id NUMBER ) NO INMEMORY;SELECT table_name, inmemory, inmemory_priority, inmemory_distribute, inmemory_compression, inmemory_duplicate FROM user_tables ORDER BY table_name;
Surrogate keys can be used to join a dimension table to a fact table in a star schema database.
A surrogate key is a unique identifier for an entity or object in a database that's used when natural keys are not available.
Surrogate keys are system generated, so it's not possible to create or store a duplicate value.
Dimension Tables
Fact Tables
Dimension Tables and Fact Tables are inter-related in 2 ways--
1. Star Schema.
2. Snowflake Schema.
OLAP Cube is a multi-dimensional data..
Four types of analytical operations in OLAP are:-
1. Roll-up -- Summarize the data / Summation
2. Drill-down - Reverse of roll-up / Detail view
3. Slice and dice - Project and Select
4. Pivot (rotate) - Re-orient the cube./ Transpose
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-INDEX.html
Normal indexes. (By default, Oracle Database creates B-tree indexes.), used for high cardinality column.
Bitmap indexes, are small in size , used for low cardinality column., used in OLAP system where modification is rare as it locks the entire table.
Partitioned indexes, which consist of partitions containing an entry for each value that appears in the indexed column(s) of the table.
Function-based indexes, which are based on expressions. They enable you to construct queries that evaluate the value returned by an expression, which in turn may include built-in or user-defined functions.
Domain indexes, which are instances of an application-specific index of type indextype.
Refer https://tipsfororacle.blogspot.com/2017/02/index-usage-with-like-operator-and.html
PIN Packages in the Shared Memory Pool-
DBMS_SHARED_POOL.KEEP ( name Varchar2, flag char default 'P');
Configure Keep Cache in Oracle-
Usually small objects should be kept in Keep buffer Cache . DB_KEEP_CACHE_SIZE initialization parameter is used to create keep buffer pool. If DB_KEEP_CACHE_SIZE is not used then no Keep Buffer is created.
Step 1. Check the current keep cache size
SQL> show parameter keep;
Step 2. Check the table size need to keep in cache.
SQL> select bytes/1024/1024 from dba_segments where segment_name='&Tablename';
Step 3. Configure Keep cache
SQL> alter system set db_keep_cache_size = 7G scope=both;
Step 4. Move the table into cache.
SQL> Alter table table_owner.table_name storage(buffer_pool Keep);
Step 5. Check the table is part of the keep pool using below query
SQL> select segment_name, buffer_pool from dba_segments where segment_name=&Table_name;
SQL> select segment_name, segment_type from dba_segments where buffer_pool='KEEP' and segment_type='TABLES' ;
After moving objects to keep cache you can observe the performance and check the "segment ordered by logical reads" in segment statistics of AWR Report.
By pinning objects you can reduce /eliminate IOs. You can make response time for specific query predictable .
Server Result Cache
A result cache is an area of memory , in the shared global area(SGA) that stores the result of the database query for reuse.
SQL> select /*+ RESULT_CACHE */ dept_id , avg(salary) FROM hr.employees
GROUP BY department_id;
Good candidate for caching are queries that access a high number of rows but return a small number of rows, such as those in datawarehouse.
https://docs.oracle.com/en/database/oracle/oracle-database/19/tgdba/tuning-result-cache.htm
https://asktom.oracle.com/ords/f?p=100:11:0::::P11_QUESTION_ID:9528615800346614204
select * from dba_network_acls;
grant execute on DBMS_DEBUG_JDWP to <User>
grant DEBUG CONNECT SESSION , DEBUG ANY PROCEDURE to <USER>;
begin
dbms_network_acl_admin.append_host_ace
(host=>'127.0.0.1',
ace=> sys.xs$ace_type(privilege_list=>sys.XS$NAME_LIST('JDWP') ,
principal_name=>'<USER>',
principal_type=>sys.XS_ACL.PTYPE_DB) );
end;
/
exec dbms_network_acl_admin.drop_acl( ACL_NAME);
https://tipsfororacle.blogspot.com/2017/02/index-usage-with-like-operator-and.html?m=1
Create procedure (dept_in Number, emp_ref_cur SYS_REFCURSOR)
IS
Begin
Open emp_ref_cur FOR select * from employees where dept_id = dept_in;
If emp_cur%rowcount = 0 Then
....
End if;
End;
/
The Bulk Exception are used to save the exception information and continue processing .
In order to bulk collect exception information, we use FORALL clause with SAVE EXCEPTIONS keyword.
All exceptions raised during execution as saved in %BULK_Exception attribute.
SQL%BULK_EXCEPTION(i).ERROR_CODE hold the corresponding Oracle error code.
SQL%BULK_EXCEPTION(i).ERROR_INDEX holds the iteration number of the FORALL statement.
SQL%BUK_EXCEPTION.COUNT holds the total number of exceptions encountered.
Select * BULK COLLECT INTO v_collections
From TestTable;
FORALL idx IN v_collections.FIRST .. v_collections.LAST SAVE EXCEPTIONs
.....
.....
EXCEPTION
WHEN OTHERS THEN
FOR idx IN 1.. SQL%BULK_EXCEPTIONS.COUNT
LOOP
v_ind := SQL%BULK_EXCEPTIONS(idx).Error_index ;
Dbms_output.Put_Line('Error encountered at '||SQL%BULK_EXCEPTIONS(idx).Error_index);
Dbms_output.Put_Line('Values '||v_collections(v_ind).Emp_id
|| v_collections(v_ind).First_name
|| v_collections(v_ind).Last_name );
Dbms_output.Put_Line('Oracle Error Ora-Code '||SQL%BULK_EXCEPTIONS(idx).Error_code);
End Loop;
End;
| |||
Materialized View
Query rewrite feature of Materialized view depends on 2 parameters:
1. QUERY_REWRITE_INTEGRITY = { enforced | trusted | stale_tolerated } , This determines the degree to which Oracle must enforce query rewriting .
enforced =Oracle enforces and guarantees consistency and integrity.
stale_tolerated Materialized views are eligible for rewrite even if they are known to be inconsistent with the underlying detail data.
2. QUERY_REWRITE_ENABLED = {TRUE | False }
Realtime Materialized View can be created using ENABLE QUERY COMPUTATION keyword, it nake use of mview logs and base table.
Tips for Materialized view
https://danischnider.wordpress.com/2019/02/18/materialized-view-refresh-for-dummies/
How does the METHOD_OPT parameter work?
The METHOD_OPT parameter is probably the most misunderstood parameter in the DBMS_STATS.GATHER_*_STATS procedures. It’s most commonly known as the parameter that controls the creation of histograms but it actually does so much more than that. The METHOD_OPT parameter actually controls the following,
The METHOD_OPT parameter syntax is made up of multiple parts. The first two parts are mandatory and are broken down in the diagram below.
The leading part of the METHOD_OPT syntax controls which columns will have base column statistics (min, max, NDV, number of nulls, etc) gathered on them. The default, FOR ALL COLUMNS, will collects base column statistics for all of the columns (including hidden columns) in the table. The alternative values limit the collection of base column statistics as follows;
The SIZE part of the METHOD_OPT syntax controls the creation of histograms and can have the following settings;
AUTO, REPEAT, SKEWONLY, Size Must be in the range [1,254].
The second part of the parameter setting needs to specify that a histogram is needed on the CUST_ID column.
https://blogs.oracle.com/optimizer/post/how-does-the-method-opt-parameter-work
PARALLEL HINT
Parallel Hint instructs the optimizer to make use the given degree of parallelism in processing the sql statement parallelly, instead of processing it serially.
Parallel execution can improve the performance of the query by dividing the work among multiple processers or cpus, Thus reducing the overall cost of Execution Plan. but you have to keep in mind that you MUST have enough CPU power available on the server, or else it can cause performance issues.
Also Parallel execution can consume a significant amount of system resources, including CPU and memory.Therefore, it is important to monitor the system’s resources when using parallel execution and to adjust the degree of parallelism as necessary.
Refer https://expertoracle.com/2022/12/04/parallel-hint-in-oracle-database-ways-to-use-and-how-to-monitor/
Every so often, we need to rebuild indexes in Oracle, because indexes become fragmented over time. This causes their performance - and by extension - that of your database queries, to degrade.
There are 2 rules of thumb to help determine if the index needs to be rebuilt:
To re-create an existing index or one of its partitions or subpartitions. If the index is marked unusable, then a successful rebuild will mark it usable.
ALTER INDEX [schema.]index REBUILD /ONLINE / PARTITION partition
Refer Blog Theme - Details (oracle.com)
To start the session trace, you first need to know the unique identifiers of the session: the SID and the SERIAL#. Use the following query
select sid, serial# from v$session where username = 'SH';
Using the SID and SERIAL# returned, turn on tracing for the session by executing the following SQL statement as the SYS user:
begin
dbms_monitor.session_trace_enable (
session_id => <SID>,
serial_num => <serial#>,
waits => true,
binds => true
plan_stat => 'all_executions');
end;/
After tracing is enabled, the trace file— a text file—is generated on the database server at the location specified by the DIAGNOSTIC_DEST database initialization parameter. For RAC Server there can be multiple location.
Now execute the activities need to be Traced...
Then close / disable the Tracing thru dbms_monitor.session_trace_disable.Run the Tkprof upon the raw trace file xe_ora_<spid>.trc that is generated.
>Tkprof xe_ora_4736.trc xe_ora_4736.out sys=no waits=yes aggregate=no width=180.
Code listing 3: tkprof output, part 1
... output truncated ... SELECT sum(s.amount_sold) AS dollars FROM sales s , times t WHERE s.time_id = t.time_id AND t.calendar_month_desc = '1998-05' call count cpu elapsed disk query current rows ————— ———————— ———————— —————————— ————————— —————————— —————————— ——————————— Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.01 0 3 0 1 ————— ———————— ———————— —————————— ————————— —————————— —————————— ——————————— total 4 0.00 0.01 0 3 0 1 Rows (1st) Rows (avg) Rows (max) Row Source Operation —————————— —————————— —————————— ——————————————————————————————————————————— 1 1 1 SORT AGGREGATE (cr=3 pr=0 pw=0 time=61 us) 1 1 1 MAT_VIEW REWRITE ACCESS FULL CAL_MONTH_ SALES_MV (cr=3 pr=0 pw=0 time=52 us cost=3 size=15 card=1) ... output truncated …..