[SQL] Oracle regular expression test

http://www.oracle.com/technology/obe/obe10gdb/develop/regexp/regexp.htm

https://forums.oracle.com/thread/2148778
------------------------------------------------------------------------------

create table TestTable(

 ID                    VARCHAR2(4 BYTE)         NOT NULL,

 Description           VARCHAR2(30 BYTE)

 );

insert into TestTable (ID, Description) values('1','1234 5th Street');

insert into TestTable (ID, Description) values('2','1 Culloden Street');

insert into TestTable (ID, Description) values('3','1234 Road');

insert into TestTable (ID, Description) values('4','33 Thrid Road');

insert into TestTable (ID, Description) values('5','One than another');

insert into TestTable (ID, Description) values('6','2003 Movie');

insert into TestTable (ID, Description) values('7','Start With Letters');

commit;

/

insert into testTable values ('1' , 'mn');

insert into testTable values ('1' , 'nn');

insert into testTable values ('1' , 'nmmmn');

insert into testTable values ('1' , 'Alaxendar');

insert into testTable values ('1' , 'Alexender');

SQL>

SQL> select * from TestTable;

ID   DESCRIPTION

---- ------------------------------

1    1234 5th Street

2    1 Culloden Street

3    1234 Road

4    33 Thrid Road

5    One than another

6    2003 Movie

7    Start With Letters

[] - matching any one of the expressions represented in the list

SELECT * FROM testTable WHERE REGEXP_LIKE(description,'[*]'); --no row selected

SELECT * FROM testTable WHERE REGEXP_LIKE(description,'[A*]');

SELECT * FROM testTable WHERE REGEXP_LIKE(description,'[^Alaxendar]');

SELECT * FROM testTable WHERE REGEXP_LIKE(description,'[^Alexender]');

SELECT * FROM testTable WHERE REGEXP_LIKE(description,'[^Alaxend.r]');

SELECT * FROM testTable WHERE REGEXP_LIKE(description,'[$]');

SELECT * FROM testTable WHERE REGEXP_LIKE(description,'[mmmm]');--任何包含m的都秀出來

SELECT * FROM testTable WHERE REGEXP_LIKE(description,'[^m(n)]'); --任何包含m or n 的都不秀出來

SELECT * FROM testTable WHERE REGEXP_LIKE(description,'[^$]');  --如果是null紀錄才不會秀出來

SELECT * FROM testTable WHERE REGEXP_LIKE(description,'[ALE|ax.r]');

---
另外regular expression 也可以被建在functional index, 如下為例:

Scott@my11g SQL>create table test (name varchar2(30));

Table created.

Scott@my11g SQL>create index myfbi on test(case when regexp_like(name,'^98721[0-9]*[5]+[0-9]*$') then 1 else 0 end);

Index created.

Scott@my11g SQL>explain plan for
  2  select * from test where case when regexp_like(name,'^98721[0-9]*[5]+[0-9]*$') then 1 else 0 end = 1;

Explained.

Scott@my11g SQL>/

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 140237472

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    20 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST  |     1 |    20 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | MYFBI |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(CASE  WHEN  REGEXP_LIKE ("NAME",'^98721[0-9]*[5]+[0-9]*$')
              THEN 1 ELSE 0 END =1)

Note
-----
   - dynamic sampling used for this statement (level=2)

19 rows selected.

Oracle Database/SQL Tuning 的幾個方法

*補充一下 以下為本人意見 跟任何資料庫理論無關、也跟任何原廠課程無關 
所以您可能在業界聽都沒聽過XD


下圖是Oracle Tuning Scope 的範圍  :

在所有的效能問題調教中, 
SQL Tuning往往占了60%的部分、
20%的部分是在架構問題以及物件設計、
而Instance Tuning占了15% , 
OS Tuning占了5%.

往往資料庫慢，DBA是最可憐的，可能被第一個怪罪，
因為必須要舉證是哪邊有問題，所以也要知道AP端是否下了太多無謂的select * 選擇太多欄位或JOIN 或subquery...沒有用綁定變量等等...
所以厲害一點的DBA也要懂得建議AP組有哪些SQL不對，甚至能夠協助改寫或優化SQL

建議在Tuning的順序要  由內而外開始調教 , 當然如果Instance 完全沒Tuning , 是一定不能用在商業運轉的...除非Server Loading 不重 , 從效益來看 , SQL tuning 與 Object Tuning 的效果最佳!!!

1.SQL Tuning : 從各表格條件中查出無謂的查詢條件 , 或重複的查詢條件

              減少查詢的區間 , 或透過where 的改寫 減少result set 的筆數

              比較常用的就是使用以下幾個技巧:

          1.1 避免SQL下重複比對條件
          1.2使用rownum減少查詢筆數
          1.3少用select distinct...(隱含sorting)
          1.4用union all取代union...(union 會把重複資料去除 , 隱含 sorting)
          1.5用NOT EXISTS取代NOT IN (排除小表格資料, 邏輯判斷較快)
          1.6用Inner Join取代Sub-query
          1.7用UNION取代OR
          1.8小心使用Like 語法 (會造成 full table scan /index fast full scan)

  其他技巧

   SQL Hint :  
                       i.force SQL to execute by specific index.

                         觀察where 的條件中, 是否可以強制選用某些索引. 或是使用/*+ FIRST_ROWS */ hint 把JOIN結果一筆一筆先顯示出來.                           

                       ii. Join two tables with different algorithm ( nested loop , merge join , hash join)
                         之前做join tuning 案例 :  http://jaychu649.blogspot.tw/2012/07/oracle-sql-outer-join-performance-tuning.html
                       iii. Other purposes...

詳細用法可以參考http://jaychu649.blogspot.tw/2012/01/hints-of-help-influnce-behaviour-of.html

   SQL profile : (for Oracle 10g+)  , select proper execution plan for specific SQL.

詳細用法 可以參考http://jaychu649.blogspot.tw/2012/08/oracle-10g-sql-tuning-advisor.html

2.Object Tuning : 
              其中隱含著ER-model的設計可能須調整 , 或可減少資料庫表格正規化的程度(正規化太嚴重,資料就會存在幾個不同的表格, 下SQL就常常要去JOIN) , 特別是現在硬碟空間相對便宜, 做正規化節省空間的優點早已不在...舉例如下:

              2.1 Analyze table/index , rebuild index ... etc.

              2.2 Migrate big tables to partition tables.

              2.3 Design Materialized views.
              2.4 Enable 表格壓縮功能(減少Disk IO , 但會增加CPU loading)

3.Instance Tuning : 
  3.1 針對資料庫參數做調教. 如果SGA、PGA採預設值不去優化 , Oracle資料庫跑的慢 是可想而知的.
  3.2 11g以後的DB 建議可以enable huge pages , 先把作業系統中挖一塊記憶體給Instance 的SGA用.
         https://jaychu649.blogspot.tw/2015/12/linux-transparent-huge-pageszy.html

4.Server and Network Tuning:
      OS Kernel Tuning : Oracle Installation Guide 會建議相關OS 的設定值
      Network Tuning : 網路Read / Write buffer , TCP tuning...etc

Ref:
http://dbak.com/wp-content/uploads/2011/02/Automatic-SQL-Tuning-in-Oracle-Database-10g-and-11g.pdf

[SQL tuning] Oracle 使用 NVL function 取代 原有的 OR 陳述式

Tip:  使用 NVL function 取代 原有的 OR 陳述式 , 關鍵在於減少重複計算.

Ref:

http://www.dbatools.net/experience/filter_at_index_level.html

 I create a composite index (DEPTNO and ENAME columns) on table SCOTT.EMP. Then I run the following SQL.

SQL> SELECT /*+ first_rows no_expand */ * FROM EMP
   2 WHERE deptNO=10 AND (ENAME IS NULL OR ENAME > 'A');

| Id  | Operation                   | Name    | Rows  | Bytes |
|   0 | SELECT STATEMENT            |         |     3 |   330 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| EMP     |     3 |   330 |
|*  2 |   INDEX RANGE SCAN          | IDX_EMP |     3 |       |

   1 - filter("ENAME" IS NULL OR "ENAME">'A')
   2 - access("DEPTNO"=10)

    Oracle is accessing the composite index by DEPTNO column only, and do a table level filtering, this is not effective enough. So I rewrite this SQL with NVL function, and check the plan again.

SQL> SELECT /*+ first_rows no_expand */ * FROM EMP 
   2 WHERE deptNO=10 AND NVL(ENAME,'B') > 'A';

| Id  | Operation                   | Name    | Rows  | Bytes |
|   0 | SELECT STATEMENT            |         |     3 |   330 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP     |     3 |   330 |
|*  2 |   INDEX RANGE SCAN          | IDX_EMP |     1 |       |

   2 - access("DEPTNO"=10)
       filter(NVL("ENAME",'B')>'A')

    In a real case, we get the SQL run much faster than before by rewriting it with NVL function.

Oracle SQL outer join 效能調教(performance tuning)

http://oracle-performance-tuning-tips3-dass.blogspot.tw/2011/02/nested-loop-outer-join-oracle-tuning.html

看到一篇文章 先引用起來

如果SQL中有兩個table outer join 有太多nested loop的問題，造成cost很高 , 可以採用以下Oracle 建議的hint 測試

To perform this outer join, 3 different join techniques have been provided by oracle.

·   Nested loop outer join  :  USE_NL(tab a b)

·   Hash outer join : USE_HASH(tab a b)

·   Sort merge outer join : USE_MERGE(tab a b])

@20120719 心得:

Oracle厲害的地方可不少, 經過一個SQL改寫後 再加上/*+ FIRST_ROWS */ hint  , 

SQL 就從原本的兩分鐘 , 加速到一秒就吐出(output)結果.

@20140512
Nested loop 是CBO最終挑選用迴圈來join, 透過index tree來fetch資料, 如果表格一大一小可能會做很快, 但表格都很大會做很久.
Hash join 就是用hash function去做, 當表格數量相當, 或數量都很大時會用到, 所需要的記憶體較多
Merge join 需要較少的記憶體, 適用於大表格處理

@20150310
Nested loop join :
適用兩個小的表格做join

Hash join :
1.適用兩個大table, 或是一小, 一大表格的join
使用小表格中的pk, 擁有不多的distinct value, 會產生一個hash table到memory, 讓
表格的join 速度加快
2.適用equal join (join 的欄位使用=等號)

Merge join :

適用於 non-equal join( > , < , <=) , 或是已經sort 過的資料表格(透過index range scan撈到的資料)

[SQL] in 與 exists 的效能比較

測試環境為 Oracle 10g DB

建立相關表格 tb1 , tb2

SQL>

create table test.tb1 as select * from dba_objects;

create table test.tb2 as select * from dba_objects;

SQL1:

set autotrace traceonly exp stat

select object_name from test.tb1 T1 where exists (select object_name from test.tb2 T2 where T1.owner=T2.owner);

Execution Plan

----------------------------------------------------------

Plan hash value: 1478961495

----------------------------------------------------------------------------

| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |      | 35628 |  3479K|   639   (3)| 00:00:08 |

|*  1 |  HASH JOIN          |      | 35628 |  3479K|   639   (3)| 00:00:08 |

|   2 |   SORT UNIQUE       |      | 55040 |   913K|   163   (2)| 00:00:02 |

|   3 |    TABLE ACCESS FULL| TB2  | 55040 |   913K|   163   (2)| 00:00:02 |

|   4 |   TABLE ACCESS FULL | TB1  | 55422 |  4492K|   163   (2)| 00:00:02 |

----------------------------------------------------------------------------

SQL2:

set autotrace traceonly exp stat

select object_name from test.tb1 T1 where T1.object_name in (select object_name from test.tb2 T2);

Execution Plan

----------------------------------------------------------

Plan hash value: 1669325194

--------------------------------------------------------------------------------

-----

| Id  | Operation            | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time

    |

--------------------------------------------------------------------------------

-----

|   0 | SELECT STATEMENT     |      | 50011 |  6446K|       |   738   (2)| 00:00

:09 |

|*  1 |  HASH JOIN RIGHT SEMI|      | 50011 |  6446K|  4200K|   738   (2)| 00:00

:09 |

|   2 |   TABLE ACCESS FULL  | TB2  | 55040 |  3547K|       |   163   (2)| 00:00

:02 |

|   3 |   TABLE ACCESS FULL  | TB1  | 55422 |  3572K|       |   163   (2)| 00:00

:02 |

--------------------------------------------------------------------------------

-----

insert into test.tb1 select * from dba_objects;

insert into test.tb1 select * from dba_objects;

insert into test.tb1 select * from dba_objects;

insert into test.tb1 select * from dba_objects;

insert into test.tb1 select * from dba_objects;

insert into test.tb1 select * from dba_objects;

insert into test.tb1 select * from dba_objects;

insert into test.tb1 select * from dba_objects;

commit;

analyze table test.tb1 compute statistics;

analyze table test.tb2 compute statistics;

SQL>

set autotrace off

col segment_name for a15

select segment_name , bytes/1024/1024 MB from dba_segments where segment_name in ('TB1','TB2') and owner='TEST';

SEGMENT_NAME            MB

--------------- ----------

TB1                     51

TB2                      6

v 當T1 塞多一點資料後 發現比較容易比較出差異性

v T1 資料 遠大於 T2 時 感受較深

v exists 是每遇到一筆 , 就吐回資料

v in 是先撈取所有資料, 存放在temp tablespace 後, 再做比對.

SQL1: cost 1599

SQL2: cost 1961

Hints of an help influnce the behaviour of the Cost Based Optimizer.

http://www.orafaq.com/tuningguide/hints.html#ordered

Hints

Hints are comments embedded in SQL that can help influnce the behaviour of the Cost Based Optimizer.

Hints are always specified immediately after the first word of a SQL statement. eg.

SELECT /*+ place your hint here*/ column_name ...
FROM table_name

The table below contains:

• Hints for Access Methods
• Hints for Join Orders
• Hints for Join Operations
• Hints for Parallel Execution
• Additional Hints

Hint	Purpose	Use when...
Hints for Access Methods
FULL(tab)	Force a Full Table Scan on tab.	Used to stop Oracle from performing an index scan.
ROWID(tab)	Force a table access by Rowid on tab	Given an equals condition on a rowid, Oracle will alwayse use it. This hint is used to force a Rowid Range scan on tab.
CLUSTER(tab)	Force a cluster scan on tab	This would be rare. A cluster scan is pretty good, so Oracle will normally select it automatically. If it doesn't, this hint will force a cluster scan.
HASH(tab)	Force a hash access on tab if tab is hash clustered.	Typically an equals predicate on a hash clustered table will always use hash access, unless the table is very small indeed. This hint may be required if accessing a hash clustered table via an IN list, or an IN subquery
INDEX(tab [ ind ...])	Force an index scan on table tab	Specifying just the table name (or alias) is the preferred method of stopping a Full Table Scan. If the statistics are calculated against the tables and indexes, Oracle should choose the best available index. The second form is dangerous, as it assumes the name of the index to be used will not change. Only use it if there are many indexes and Oracle will not choose the right one. Better yet, use NO_INDEX to disable the index you want to avoid. If you supply multiple indexes, Oracle will usually choose the best one from the list specified. Beware though that you don't fall into theAND-EQUAL trap.
INDEX_COMBINE(tab [ ind ...])	Forces a bitmap index access path on tab	Primarily this hint just tells Oracle to use the bitmap indexes on table tab. Otherwise Oracle will choose the best combination of indexes it can think of based on the statistics. If it is ignoring a bitmap index that you think would be helpful, you may specify that index plus all of the others taht you want to be used. Note that this does not force the use of those indexes, Oracle will still make cost based choices.
INDEX_JOIN(tab [ ind ...])	Use the Index Join technique to avoid a table access.	All columns in your SQL for a given table are contained in two or more indexes. Oracle can merge the indexes to avoid a table lookup. If there are different possible combinations of indexes that could be used, specify the index names as well if there is a particular combination that would be faster.
INDEX_DESC(tab [ ind ...])	Same as the INDEX hint, except process range scans in descending order	Use this hint if you are using an index to sort rows instead of an ORDER BY.
INDEX_FFS(tab [ ind ...])	Forces a Fast Full Scan on one of tab's indexes	If all columns required for a SQL reside in one index, then a Fast Full Scan may be used instead of a Full Table Scan to avoid a table access.
NO_INDEX(tab [ ind ...])	Forces Oracle to ignore indexes	Used with just the table name (or alias), Oracle will ignore all indexes on that table. This is equivalent to a FULL hint unless the table is clustered. If index names are specified, they will not be used. If Oracle has two indexes to choose from, this could be used to disable an index, instead of using the INDEX hint to force the use of the other index.
AND_EQUAL(tab ind ind [ ind...])	Forces Oracle to scan all nominated single column indexes used in AND col = ... predicates	Don't use this. You will probably never come across a good implementation of this technique. See the AND-EQUAL trap.
USE_CONCAT	Expand OR predicates or IN lists into UNIONs	Each predicate in the list of ORs can individually use and index, and collectively the ORs return less than 4% of the table. Also useful in a join query where each of the OR predicates is indexed and on a different table.
NO_EXPAND	Stops Oracle from expanding ORs and IN lists into UNIONs. See USE_CONCAT.	If in Explain Plan you see that Oracle is expanding ORs or IN lists into UNIONs, and you think a full table scan would be faster because the UNIONs collectively return more than 4% of the table, then use this hint to check it out.
REWRITE([view ...])	Forces Oracle to resolve the query using a meterialized view instead of the tables in the FROM clause.	Use when the materialized view resolves the same joins or aggregates as are used in the query.
NO_REWRITE	Forces Oracle to stop using query rewrite.	Use when the session or database parameter QUERY_REWRITE_ENABLED is set to true, but you want to avoid using the materiazed view because it may be out of date.
Hints for Join Orders
ORDERED	Join the tables in the FROM clause in the order they are specified	Use if Oracle is joining table in the wrong order. Can also be used to encourage Oracle to use a non-correlated WHERE col IN sub-query as the driving table in a SELECT and then join back to the outer table. If you just want to suggest the best table to lead the join, try the LEADING hint instead.
STAR	Forces Oracle to use a star query plan.	Avoid using this. Star queries are deprecated in favour of STAR_TRANSFORMATION which uses bitmap indexes in favour of cartesian joins. See Star Query.
Hints for Join Operations
USE_NL(tab [tab..])	Use a Nested Loops join	Use when Oracle is using a Hash or Sort Merge join (high volume SQLs), and you want it to use a Nested Loops join (low volume SQLs). Older versions of Oracle required this hint to be used in conjunction with the ORDERED hint. This is still advisable to avoid unexpected results.
USE_MERGE(tab [tab..])	Use a Sort-Merge join on tab	Use when Oracle is using a Nested Loops join, and you have a high volume join using range predicates. Older versions of Oracle required this hint to be used in conjunction with the ORDERED hint. This is still advisable to avoid unexpected results.
USE_HASH(tab [tab..])	Use a Hash join on tab	Use when Oracle is using a Nested Loops or Merge join, and you have a high volume join using equals predicates. Older versions of Oracle required this hint to be used in conjunction with the ORDERED hint. This is still advisable to avoid unexpected results.
DRIVING_SITE(tab)	Forces Oracle to evaluate a join involving a remote table on the remote table's database.	Firstly, try not to join to remote tables. If you must, use this hint when you are joining a local table to a remote table, and the local table is smaller. See Remote Table.
LEADING(tab)	Forces tab to be the leading table in a join	Use instead of the ORDERED hint if you only want to suggest the best starting table. Oracle can have trouble choosing a leading table if there a two of more in the SQL with non-indexed WHERE clauses.
HASH_AJ	Use a Hash Anti-Join to evaluate a NOT IN sun-query.	Use this when your high volume NOT IN sub-query is using a FILTER or NESTED LOOPS join. See High Volumne Nested Loops Joins. Check Explain Plan to ensure that it shows HASH JOIN (ANTI). Try MERGE_AJ if HASH_AJ refuses to work. The HASH_AJ hint is sepcified from within the sub-query, not in the main SQL statement.
MERGE_AJ	Use a Merge Anti-Join to evaluate a NOT IN sun-query.	Use this when HASH_AJ does not work. MERGE_AJ will probably not work either, but it's worth a try.
HASH_SJ	Use a Hash Semi-Join to evaluate a correlated EXISTS sub-query.	Use this when you have a high volume outer query, and a correlated single table sub-query with equals joins back to the outer query, and no DISTINCT / GROUP BY clause. Check Explain Plan to ensure that it shows HASH JOIN (SEMI). Try MERGE_SJ if HASH_SJ refuses to work. The HASH_SJ hint is sepcified from within the sub-query, not in the main SQL statement.
MERGE_SJ	Use a Merge Semi-Join to evaluate a correlated EXISTS sub-query.	Use this when HASH_SJ does not work. MERGE_SJ will probably not work either, but it's worth a try.
Hints for Parallel Execution
Parallel Query hints have been deliberately omitted because they are a lazy way to tune and wreak havoc for DBAs if over-used. Speak to your DBA about using parallel query.
Additional Hints
APPEND	Direct Path Insert	Use Direct Path data load to append inserted rows to the end of the table, rather than searching for free space in previously used data blocks.
CACHE	Cache blocks from Full Table Scan	Usually Full Table Scans will not bump other blocks out of cache, the theory being that they probably won't be used again. Use this hint if you are going to perform another Full Table Scan on the same table straight away.
NO_CACHE	Do not cache blocks from a Full Table Scan	This is the default behaviour, so you should never need it. Perhaps if the CACHE hint were hard coded into a view, the NO_CACHE hint on a select from the view would override it. Just guessing.
MERGE	Enables Complex View Merging	Use when you join to a view that contains a GROUP BY or DISTINCT. See Selecting from Views
NO_MERGE	Disable Complex View Merging	Complex View Merging is a good thing. Don't use this hint unless you are curious to see how much faster complex view merging can be.
UNNEST	A global panacea for badly written sub-queries. Can be used in place of Anti-joins and Semi-joins if you are not really sure what you're doing.	If you can't get your sub-query to stop using a FILTER step, try UNNEST. It uses internal cleverness to rewrite your query.
NO_UNNEST	Forces Oracle not to Unnest sub-queries.	If UNEST_SUBQUERY initialisation parameter is set, Oracle will automatically try to unnest sub-queries. Use this hint to stop it from doing that for a particular sub-query.
PUSH_PRED(view)	Push a join predicate between a view (or inline view) and a table into the view.	Use with a Nested Loop join to a view when the view is the outer (2nd) table in the join. The join condition will be pushed into the view, potentially enabling an index use. See Selecting from Views.
NO_PUSH_PRED(view)	Stop Oracle from pushing join predicates.	Pushing Join Predicates is a good thing - don't use this hint.
PUSH_SUBQ	Force Oracle to evaluate sub-query before other non-indexed predicates.	Use this if you have lots of non-indexed predicates, most of which almost always come out true, and a non-merged sub-query that reduces the number of rows significantly. The performance benefit will only be noticeable over larger data volumes. Over those volumes you will probably be better off merging the sub-query (see the UNNEST hint).
STAR_TRANSFORMATION	Use bitmap indexes for a Star Transformation execution path.	Use this when joining a fact table with bitmap indexes to dimension tables keyed by those bitmap indexed columns. See Star Query.
ORDERED_PREDICATES	Execute the non-indexed non-join predicates in the order in which they are supplied.	If one predicate eliminates a row for a query, Oracle does not evaluate the others. If you order your predicates with the ones most likely to fail first, then this hint can reduce the total number of predicates evaluated. Also see PUSH_SUBQ.

---

©Copyright 2003

Oracle table drop syntax

Oracle® Database SQL Reference 10g Release 2 (10.2)
-

drop table table_name;
 只去除table 的metadata,還是會佔空間

drop table table_name truncate;
 比砍掉所有row data,比drop table 在recreate table更有效率,
 會把相關indexes一併truncate掉，
 還是會佔用MINEXTENTS storage in table definition.

drop table table_name purge;
 (drop the table and release the space in one step)

drop table table_name CASCADE CONSTRAINTS ;
 drop 跟primary key , unique key 等等相關的 integrity constraints,若不下此選
 項，又有integrity constraints存在，drop table則會失敗

-
P.S. drop 語法為DDL 不會產生大量redo