Oracle PL/SQL

A nonpartitioned table can have partitioned or nonpartitioned indexes.
A partitioned table can have partitioned or nonpartitioned indexes.
The partitioning key is comprised of one or more columns that determine the partition where each row will be stored.
To reduce disk usage and memory usage (specifically, the buffer cache), you can store tables and partitions of a partitioned
table in a compressed format inside the database. This often leads to a better scaleup for read-only operations. Table compression
can also speed up query execution. There is, however, a slight cost in CPU overhead.

Just like partitioned tables, partitioned indexes improve manageability, availability, performance, and scalability.
They can either be partitioned independently (global indexes) or automatically linked to a table's partitioning method (local indexes).
In general, you should use global indexes for OLTP applications and local indexes for data warehousing or DSS applications.
Also, whenever possible, you should try to use local indexes because they are easier to manage.
1. If the table partitioning column is a subset of the index keys, use a local index.
2. If the index is unique and does not include the partitioning key columns, then use a global index.
3. If your priority is manageability, use a local index.
4. If the application is an OLTP one and users need quick response times, use a global index. If the application is a
DSS one and users are more interested in throughput, use a local index.
(My note: prefer to use local index unless the index is unique and not part of the partitioned column)

unique column can have multiiple null values
The explain plan is what the optimizer thinks will happen when you run, the execution plan is actually happened when you ran the query.
Well, Oracle isn't quite that smart. In this case, Oracle knew (or in most cases it's a good guess) we would process 100 rows because we
generated statistics on the EMP table after we created the table. The optimizer uses these statistics to generate execution plans.

the autotrace statistics reported however are FACTS, after executing the statement, not guesses, not part of optimization.
SQL does not treat NULL values as zeros when calculating SUM, it ignores them.
This makes a difference only in one case - when the sequence being totalled up does not contain numeric items, only NULLs: if at least one number is present, the result is going to be numeric.

From official:
Autotrace report is generated after successful SQL DML (that is, SELECT, DELETE, UPDATE and INSERT) statements.
The execution plan shows the SQL optimizer's query execution path.
The statistics are recorded by the server when your statement executes and indicate the system resources required to execute your statement.

The maximum length string that SQLERRM will return is 512 bytes, call DBMS_UTILITY.FORMAT_ERROR_STACK for full message.
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE to see the line number.
A left outer join returns all the values from an inner join plus all values in the left table that do not match to the right table.
The read committed transaction isolation level is the Oracle default
Indexed TABLE:
DECLARE
TYPE ename_table_type IS TABLE OF emp.ename%TYPE
INDEX BY BINARY_INTEGER;
ename_table ename_table_type;
B*Tree index is used mostly (one key to one row/range) while Bitmap index is used for low cardinality data (Y,N and null).

UNION, INTERSECT and MINUS are ordered by the first column while UNION ALL is not ordered.