网上有人给出了最佳的优化思路是： 1.先将大表中满足条件的记录抽出来生成一张临时表. 2.再将这较小的临时表与另一张较小的表进行
网上有人给出了最佳的优化思路是：
1.先将大表中满足条件的记录抽出来生成一张临时表.
2.再将这较小的临时表与另一张较小的表进行关联查询.
先不论思路是否值得商榷，这把临时表当成中转站的做法还是很值得肯定
临时表本质上就是一种cache的表现形式，Oracle的临时表都是事先建好的
一旦用了临时表，存放的就是和本会话相关的数据
没有人会傻乎乎地用临时表来保存本应该共享的数据
with子查询实际上也是用了临时表，Oracle会替你创建一张临时表
因此临时表的开销WITH子查询也会有。只要把AUTOTRACE打开你就会看到REDO的开销
关于临时表的使用至少会带来两个问题：
1）主查询的执行计划问题
2）额外的写redo的问题
如果，
临时表作为复杂查询条件的中间结果用于主查询，因为临时表里往往只是个别字段的少量数据，1）的问题比较突出；
如果，
临时表作为最终展现前的结果归集，可能临时表会有比较多字段的较多数据，2）的问题比较突出
㈠ 主查询的执行计划问题
9i临时表由于动态采样level 1,还得用hint,10g比较好用
比较复杂的存储过程（比如数据抽取）可能用到临时表，比实体表优势就是redo少，，自动清除
对于临时表的缺陷--采样问题，执行计划的问题其实主要是临时表的cardinality的问题
对于临时表方案，建议动态采样。9IR2以后的版本使用DYNAMIC_SAMPLING 参数或hint能基本避免
如写上 HINT强制它采样 /*+dynamic_sampling(t 0) */
cardinality hint分段提示是个比较好的最佳实践
例如：
临时表里的数据量有大起大落的情形，Oracle只会在硬解析的时候做一次取样
当临时表数据量变化之后，原来的执行计划可能已经不是最优的
碰到这种问题建议使用动态SQL
临时表的数据量在插入结束之后可以通过SQL%ROWCOUNT得知
然后在动态SQL里面拼入cardinality提示，这个提示没有必要精确，要不然你就会有无数的硬解析了
建议给它设置的坎是5000, 即1-5000当作5000处理，5001-10000当作10000,
如此类推，CARDINALITY = CEIL(SQL%ROWCOUNT/5000)*5000，
你也可以通过测试调整出一个合理的值
MySQL: BLOB and other no-sql storage, what are the differences?May 13, 2025 am 12:14 AMMySQL'sBLOBissuitableforstoringbinarydatawithinarelationaldatabase,whileNoSQLoptionslikeMongoDB,Redis,andCassandraofferflexible,scalablesolutionsforunstructureddata.BLOBissimplerbutcanslowdownperformancewithlargedata;NoSQLprovidesbetterscalabilityand
MySQL Add User: Syntax, Options, and Security Best PracticesMay 13, 2025 am 12:12 AMToaddauserinMySQL,use:CREATEUSER'username'@'host'IDENTIFIEDBY'password';Here'showtodoitsecurely:1)Choosethehostcarefullytocontrolaccess.2)SetresourcelimitswithoptionslikeMAX_QUERIES_PER_HOUR.3)Usestrong,uniquepasswords.4)EnforceSSL/TLSconnectionswith
MySQL: How to avoid String Data Types common mistakes?May 13, 2025 am 12:09 AMToavoidcommonmistakeswithstringdatatypesinMySQL,understandstringtypenuances,choosetherighttype,andmanageencodingandcollationsettingseffectively.1)UseCHARforfixed-lengthstrings,VARCHARforvariable-length,andTEXT/BLOBforlargerdata.2)Setcorrectcharacters
MySQL: String Data Types and ENUMs?May 13, 2025 am 12:05 AMMySQloffersechar, Varchar, text, Anddenumforstringdata.usecharforfixed-Lengthstrings, VarcharerForvariable-Length, text forlarger text, AndenumforenforcingdataAntegritywithaetofvalues.
MySQL BLOB: how to optimize BLOBs requestsMay 13, 2025 am 12:03 AMOptimizing MySQLBLOB requests can be done through the following strategies: 1. Reduce the frequency of BLOB query, use independent requests or delay loading; 2. Select the appropriate BLOB type (such as TINYBLOB); 3. Separate the BLOB data into separate tables; 4. Compress the BLOB data at the application layer; 5. Index the BLOB metadata. These methods can effectively improve performance by combining monitoring, caching and data sharding in actual applications.
Adding Users to MySQL: The Complete TutorialMay 12, 2025 am 12:14 AMMastering the method of adding MySQL users is crucial for database administrators and developers because it ensures the security and access control of the database. 1) Create a new user using the CREATEUSER command, 2) Assign permissions through the GRANT command, 3) Use FLUSHPRIVILEGES to ensure permissions take effect, 4) Regularly audit and clean user accounts to maintain performance and security.
Mastering MySQL String Data Types: VARCHAR vs. TEXT vs. CHARMay 12, 2025 am 12:12 AMChooseCHARforfixed-lengthdata,VARCHARforvariable-lengthdata,andTEXTforlargetextfields.1)CHARisefficientforconsistent-lengthdatalikecodes.2)VARCHARsuitsvariable-lengthdatalikenames,balancingflexibilityandperformance.3)TEXTisidealforlargetextslikeartic
MySQL: String Data Types and Indexing: Best PracticesMay 12, 2025 am 12:11 AMBest practices for handling string data types and indexes in MySQL include: 1) Selecting the appropriate string type, such as CHAR for fixed length, VARCHAR for variable length, and TEXT for large text; 2) Be cautious in indexing, avoid over-indexing, and create indexes for common queries; 3) Use prefix indexes and full-text indexes to optimize long string searches; 4) Regularly monitor and optimize indexes to keep indexes small and efficient. Through these methods, we can balance read and write performance and improve database efficiency.
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Dreamweaver Mac version
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
EditPlus Chinese cracked version
Small size, syntax highlighting, does not support code prompt function
MinGW - Minimalist GNU for Windows
This project is in the process of being migrated to osdn.net/projects/mingw, you can continue to follow us there. MinGW: A native Windows port of the GNU Compiler Collection (GCC), freely distributable import libraries and header files for building native Windows applications; includes extensions to the MSVC runtime to support C99 functionality. All MinGW software can run on 64-bit Windows platforms.
SecLists
SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.
